Best Practice for full backup and transaction log truncation

BACKUP LOG 指令原本是用來備份log 用的，因為加上了WITH TRUNCATE_ONLY 選項，所以不會真正備份記錄檔，只會移除非使用的交易記錄。 後記. DBCC SHRINKFILE ...

No, the TRUNCATE_ONLY option was removed in 2008 and it is also not present in more recent version. So, you cannot use it nowadays.

BACKUP LOG WITH NO_LOG 和 WITH TRUNCATE_ONLY 選項已中止。 如果您要使用完整復原模式或大量記錄復原模式，而且您必須從資料庫移除記錄備份鏈結，請切換到簡單復原 ...

In the Transaction log section: For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.

Switch the database recovery mode to SIMPLE, and then use DBCC SHRINKFILE. Then restore your original recovery mode.

BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server's transaction log without really backing it up.

A full backup only backs up a small portion of the transaction log so that the data in the backup is in a consistent state. It doesn't truncate ...

Best practice: Don't truncate your transaction log, except in unanticipated emergencies where you have run out of storage.

In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed.

Trace flag 3231 in SQL Server 2000 and SQL Server 2005 will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode.